﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Globalization;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using CashCommon;
using CashResource;
using System.Data.SqlTypes;
using System.Security.Principal;

namespace CashConnect
{

    /// <summary>
    /// Класс предоставляющий доступ к базе данных Mssql. Синглетон. 
    /// Перед использованием необходимо инициализировать соедининене с базой: WFSql.Db.Connect(Data Source, dataBase, User ID, Password);
    /// </summary>
    public sealed class WFSql : IDatabaseBase, IDisposable
    {
        
        /// <summary>
        /// Получение экзепляра класса для работы с БД
        /// </summary>
        public static WFSql DB
        {
            get
            {
                if (_db == null)
                {
                    lock (_syncRoot)
                    {
                        if (_db == null)
                            _db = new WFSql();
                    }
                }
                return _db;
            }
        }
        public void Connect(string dataSource, string dataBase, string connectedUser, string password)
        {
            try
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                builder.DataSource = dataSource;
                builder.InitialCatalog = dataBase;
                builder.UserID = connectedUser;
                builder.Password = password;
                builder.MultipleActiveResultSets = true;
                if (this.IsActive())
                    this.Close();
                _connect = new SqlConnection(builder.ConnectionString);
                _connect.Open();
            }
            catch (Exception)
            {
                throw new ECashSql(MessageType.UserMessage, CommonError.Connect, "");
            }
        }
        public void ConnectWin(string serverName, string catalog, string connectedUser)
        {
            try
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                builder.DataSource = serverName;
                builder.InitialCatalog = catalog;
                builder.UserID = WindowsIdentity.GetCurrent().Name;
                builder.IntegratedSecurity = true;
                builder.MultipleActiveResultSets = true;
                if (this.IsActive())
                    this.Close();
                _connect = new SqlConnection(builder.ConnectionString);
                _connect.Open();
            }
            catch (Exception)
            {
                throw new ECashSql(MessageType.UserMessage, CommonError.Connect, "");
            }
        }
        
        public void Close()
        {
            if (_connect != null)
                _connect.Close();
        }
        public bool IsActive()
        {
            return (_connect != null) && (_connect.State != System.Data.ConnectionState.Closed);
        }
        public void CheckConnection()
        {
            if (_connect.State == ConnectionState.Closed || _connect.State == ConnectionState.Closed)
                throw new ECashSql(MessageType.AllertLog, SqlErrors.ConnectionIsError, "");
        }
        public void StartTransaction()
        {
            if (_connect == null)
                throw new ECashSql(MessageType.SilentLog, "WFSql.DB._connect is null", "");

            if (_trans != null)
                throw new ECashSql(MessageType.AllertLog, SqlErrors.ConnectInTransaction, "");

            _trans = _connect.BeginTransaction();
        }
        public bool InTransaction()
        {
            if (_trans == null)
                throw new ECashSql(MessageType.SilentLog, "WfOracle.Db._trans is null", "");

            return _trans != null;
        }
        public void Commit()
        {
            if (_trans == null)
                throw new ECashSql(MessageType.SilentLog, "WfOracle.Db._trans is null", "");
            
            _trans.Commit();
            _trans.Dispose();
            _trans = null;
        }
        public void Rollback()
        {
            if (_trans == null)
                throw new ECashSql(MessageType.SilentLog, "WfOracle.Db._trans is null", "");

            _trans.Rollback();
            _trans.Dispose();
            _trans = null;
        }
        public SqlConnection SqlConnection
        {
            get { return _connect; }
        }
        public SqlTransaction SqlTransaction
        {
            get { return _trans; }
        }
        public void AddInParameter(string name, SqlDbType sqlType, object value, SqlCommand command, bool IsNull)
        {
            SqlParameter prm = new SqlParameter(name, sqlType);
            prm.IsNullable = IsNull;
            if (!IsNull && value == null)
                throw new ECashSql(MessageType.SilentLog, SqlErrors.IsNullParam, "");
            if (IsNull && value == null)
                prm.Value = DBNull.Value;
            else
                prm.Value = value;
            command.Parameters.Add(prm);
        }
        public SqlParameter AddOutParameter(string name, SqlDbType sqlType, SqlCommand command)
        {
            SqlParameter prm = new SqlParameter(name, sqlType);
            prm.Direction = ParameterDirection.Output;
            command.Parameters.Add(prm);
            return prm;
        }

        // Properties
        /// <summary>
        /// Если коннекция активна, возвращает наименование сервера, если нет ""
        /// </summary>
        public string ServerName { get { return _connect != null && _connect.State == ConnectionState.Open? _connect.DataSource: "";} }
        
        /// <summary>
        /// Если коннекция активна, возвращает имя базы данных, если нет ""
        /// </summary>
        public string DbName { get { return _connect != null && _connect.State == ConnectionState.Open ? _connect.Database : ""; } }
        
        /// <summary>
        /// Если коннекция активна, возвращает логин пользователя, если нет ""
        /// </summary>
        public string Login 
        { 
            get
            {
                if (_connect != null && _connect.State == ConnectionState.Open)
                {
                    foreach (string s in _connect.ConnectionString.Split(';'))
                    { 
                        int i = s.IndexOf("User ID=");
                        if (i > -1) return s.Substring(i + 8); 
                    }
                }
                return "";
            } 
        }
        
        /// <summary>
        /// Если коннекция активана, возвращает имя компьютера, с которого приконнекался пользователь, если нет ""
        /// </summary>
        public string UserHost { get { return _connect != null && _connect.State == ConnectionState.Open ? _connect.WorkstationId : ""; } }

        // Private
        private static volatile WFSql _db;
        private static object _syncRoot = new Object();
        private SqlTransaction _trans;
        private SqlConnection _connect;

        // Constructors
        private WFSql(){ }

        //Dispose
        private void Dispose(bool disposing)
        {
            if (disposing)
            {
                _connect.Dispose();
            }
        }
        public void Dispose()
        {
            this.Dispose(true);
            GC.SuppressFinalize(this);
        }
    }

    public sealed class SqlWork
    {
        public static void SqlSimple(string sql, DataTable dataTable, string id)
        {
            SqlCommand cmd = new SqlCommand(sql, WFSql.DB.SqlConnection, WFSql.DB.SqlTransaction);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            dataTable.Clear();
            da.Fill(dataTable);
            dataTable.PrimaryKey = new DataColumn[] { dataTable.Columns[id] };
        }
        public static void SqlById(string sql, DataTable dataTable, string id, int value)
        {
            SqlCommand cmd = new SqlCommand(sql, WFSql.DB.SqlConnection, WFSql.DB.SqlTransaction);
            WFSql.DB.AddInParameter(id, SqlDbType.Int, value, cmd, false);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            dataTable.Clear();
            da.Fill(dataTable);
            dataTable.PrimaryKey = new DataColumn[] { dataTable.Columns[id] };
        }
        public static void SqlDeleteSimple(string sql, int[] ids, string id)
        {
            SqlCommand cmd = new SqlCommand(sql, WFSql.DB.SqlConnection, WFSql.DB.SqlTransaction);
            cmd.CommandType = CommandType.Text;

            foreach (int i in ids)
            {
                cmd.Parameters.Clear();
                WFSql.DB.AddInParameter(id, SqlDbType.Int, i, cmd, false);
                cmd.ExecuteNonQuery();
            }
        }
       
        // Constructors
        private SqlWork() { }
    }

 }
